package br.com.massai.model.dao;

import java.io.IOException;
import java.math.BigDecimal;
import java.math.MathContext;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.concurrent.locks.Lock;
import java.util.concurrent.locks.ReentrantLock;

import javax.ejb.EJB;
import javax.ejb.Stateless;
import javax.persistence.NoResultException;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

import br.com.massai.model.business.EntryBusiness;
import br.com.massai.model.dao.jdbc.ConnectionDB;
import br.com.massai.model.dao.jdbc.ConnectionPool;
import br.com.massai.model.entity.Department;
import br.com.massai.model.entity.Entry;
import br.com.massai.model.entity.EntryItem;
import br.com.massai.model.entity.GenericEntity;
import br.com.massai.model.entity.Spreadsheet;
import br.com.massai.model.exceptions.ErrorCore;
import br.com.massai.model.exceptions.SystemException;
import br.com.massai.view.enums.Regime;

@Stateless
public class SpreadsheetDAO extends GenericDAO<Spreadsheet> implements SpreadsheetRepository {

	private static final long serialVersionUID = 3155226840314442018L;
	
	private static final Logger LOG = LogManager.getLogger(SpreadsheetDAO.class);

	@EJB private ConnectionPool connectionPool;
	@EJB private EntryBusiness entryBusiness;

	@Override
	public Integer getNextCodeByDepartment(Department department) throws SystemException {
		try {
			Number code = 0;
			code = (Number) em.createNativeQuery("SELECT (MAX(s.code) + 1) AS code FROM spreadsheet s WHERE s.department_id = :department AND s.deleted =:deleted")
					.setParameter("department", department.getId())
					.setParameter("deleted", false)
					.getSingleResult();

			if(code == null)
				throw new NoResultException();
			return code.intValue();

		} catch (SystemException e) {
			e.printStackTrace();
			return 1;

		} catch (NoResultException e) {
			e.printStackTrace();
			return 1;
		}
	}

	@Override
	public void aprovedSpreadsheet(Spreadsheet spreadsheet) throws SystemException, SQLException {
		LOG.info(">>>>>>>>>>>>>>> INICIANDO APROVACAO");
		Connection conn = null;
		PreparedStatement pstm = null;
		Lock lock = new ReentrantLock();

		try {
			lock.lock();
			LOG.info(">>>>>>>>>>>>>>> TRANSACAO BLOQUEADA");

			String insert = "INSERT INTO eef (emp_codigo, efo_fol_seq, efo_epg_codigo, eve_codigo, parametro, referencia, valor, calculomanual) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";

			String emp_codigo = spreadsheet.getDepartment().getCodeEmp();
			Integer efo_fol_seq = this.getSequencialNumber(spreadsheet.getDepartment().getCodeEmp(), spreadsheet.getAccrualBasis());

			conn = connectionPool.getConnection(ConnectionDB.FORTES);
			pstm = conn.prepareStatement(insert);
			conn.setAutoCommit(false);
			
			LOG.info(">>>>>>>>>>>>>>> CONEXAO REALIZADA... INICIAR LANCAMENTOS");

			for(Entry entry : entryBusiness.getEntriesBySpreadsheet(spreadsheet)){
				LOG.info(">>>>>>>>>>>>>>> FLUXO DE LANCAMENTOS");

				if(!entry.getRegime().equals(Regime.R)){
					LOG.info(">>>>>>>>>>>>>>> LANCAMENTO PARA REGIMES DIFERENTES DE 'NORMAL'");
					String efo_epg_codigo = entry.getEmployee().getEpgCodigo();
					String eve_codigo = entry.getRegime().getCode();
	
					if((entry.getTotalAmount() == null || entry.getTotalAmount().compareTo(BigDecimal.ZERO.setScale(GenericEntity.SCALE_VALUE)) <= 0)){
						if(!entry.getRegime().equals(Regime.G)){
							entry.setTotalAmount(BigDecimal.ZERO);
							for(EntryItem entryItem : entry.getEntryItems())
								entry.setTotalAmount(entry.getTotalAmount().add(entryItem.getQuantity().multiply(entryItem.getUnitPrice(), MathContext.DECIMAL64), MathContext.DECIMAL64));
	
						} else {
							entry.setTotalAmount(entry.getValueGratification());

						}
					}

					Double valueParam = 0D;
					Double value = entry.getTotalAmount().doubleValue();
					String manual = "N"; 

					if(entry.getRegime().equals(Regime.P)){
						valueParam = entry.getTotalAmount().doubleValue();
						value = 0D;
//						manual = "N";
					}

					pstm.setString(1, emp_codigo);
					pstm.setInt(2, efo_fol_seq);
					pstm.setString(3, efo_epg_codigo);
					pstm.setString(4, eve_codigo);
					pstm.setDouble(5, valueParam);
					pstm.setDouble(6, 0D);
					pstm.setDouble(7, value);
					pstm.setString(8, manual);

					LOG.info(">>>>>>>>>>>>>>> DADOS INSERT: " + eve_codigo + " - " + valueParam + " - " + value + " - " + emp_codigo + " - " + efo_fol_seq + " - " + efo_epg_codigo);

					pstm.execute();
					LOG.info(">>>>>>>>>>>>>>> EXECUTADO");
				}
			}
			conn.commit();
			LOG.info(">>>>>>>>>>>>>>> COMMITADO");

		} catch (SystemException e) {
			LOG.error(">>>>>>>>>>>>>>> OCORREU UMA ERRO DE NEGOCIO");
			e.printStackTrace();
			throw new SystemException(e.getCore(), e, (String[]) e.getParameters().toArray());

		} catch (Exception e) {
			LOG.error(">>>>>>>>>>>>>>> OCORREU UMA ERRO GRAVE");
			e.printStackTrace();
			throw new SystemException(ErrorCore.ERROR_UNEXPECTED, e);

		} finally {
			LOG.info(">>>>>>>>>>>>>>> FINALIZANDO CONEXOES");
			if(conn != null) conn.close();
			if(pstm != null) pstm.close();
			lock.unlock();
			LOG.info(">>>>>>>>>>>>>>> TRANSACAO LIBERADA");
		}
	}

	@Override
	public Integer getSequencialNumber(String codeEmp, String accrualBasis) throws SystemException, SQLException, IOException {
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;

		try {
			String query = "SELECT MAX(CAST(fol_seq AS INT)) AS fol_seq FROM FPG WHERE emp_codigo = ? AND anomes = ?";

			conn = connectionPool.getConnection(ConnectionDB.FORTES);
			pstm = conn.prepareStatement(query);
			pstm.setString(1, codeEmp);
			pstm.setString(2, accrualBasis.substring(2, 6) + accrualBasis.substring(0, 2));
			rs = pstm.executeQuery();

			Integer sequencial = null;
			if(rs.next())
				sequencial = rs.getInt("fol_seq");

			if(sequencial == null || sequencial.equals(new Integer("0")))
				throw new SystemException(ErrorCore.ERROR_THERE_IS_NO_OPEN_SHEET, accrualBasis.subSequence(0, 2) + "/" + accrualBasis.substring(2, 6));

			LOG.info(">>>>>>>>>>>>>>> RETORNANDO SEQUENCIAL FOLHA");
			return sequencial;

		} catch (SQLException e) {
			throw new SystemException(ErrorCore.ERROR_DATABASE_CONNECTION);

		} catch (SystemException e) {
			throw new SystemException(e.getCore(), e, (String[]) e.getParameters().toArray());

		} catch (IOException e) {
			throw new SystemException(ErrorCore.ERROR_DATABASE_CONNECTION);

		} finally {
			if(conn != null) conn.close();
			if(pstm != null) pstm.close();
			if(rs != null) rs.close();
		}
	}
}